using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections;
using System.Web.UI.HtmlControls;
using System.Globalization;
using System.Web.Script.Services;
using System.Web.Services;

/// <summary>
/// A page for managing translations for applications
/// </summary>
public partial class Translations : System.Web.UI.Page
{
    private int editButtonColumNo = 3;
    private Hashtable bundles = new Hashtable();
    private SortedList<int, string> translationIds = new SortedList<int, string>();
    private static string defaultLanguage = string.Empty;
    private static string crossTabColumns = string.Empty;
    private Database db = new Database();

    /// <summary>
    /// Displays available applications and handles JavaScript for the full-window tranlation editor
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT id, displayname FROM applications ORDER BY name ASC";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                radApplication.DataSource = ds;
                radApplication.DataValueField = "id";
                radApplication.DataTextField = "displayname";
                if(!Page.IsPostBack) {
                    radApplication.DataBind();
                    if(radApplication.Items.Count > 0) {
                        radApplication.SelectedIndex = 0;
                        ShowLanguages(null, EventArgs.Empty);
                        mltMain.ActiveViewIndex = 1;
                    }
                }
            }
            crossTabColumns = string.Empty;
        } else {
            Page.ClientScript.RegisterClientScriptInclude("main", ResolveUrl("~/js/main.js"));
            Page.ClientScript.RegisterClientScriptInclude("jquery", ResolveUrl("~/js/jquery-1.2.6.min.js"));
            Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/json.js"));
            Page.ClientScript.RegisterClientScriptInclude("translationjs", ResolveUrl("~/js/translations.js"));
            Master.RenderJSArrayWithCliendIds(fullsizeholder);
            if(mltMain.ActiveViewIndex == 2) {
                PopulateAddFormLanguages();
            }
        }
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }
    
    /// <summary>
    /// Shows the languages and bundles for the application selected.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ShowLanguages(object sender, EventArgs e) {
        if(radApplication.SelectedValue == null) {
            return;
        }
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT languages, type FROM applications WHERE id = @p";
            cmd.AddParameterWithValue("@p", radApplication.SelectedValue);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            string languages = string.Empty;
            while(reader.Read()) {
                languages = reader.GetString(0);
                switch(reader.GetString(1)) {
                    case "dotnet":
                    case "dotnetcf":
                        lblBundleName.Text = "Form name";
                        break;
                    case "java":
                        lblBundleName.Text = "Bundle Name";
                        break;
                    default:
                        lblBundleName.Text = "Category";
                        break;
                }
            }
            reader.Close();
            lstLanguages.Items.Clear();
            string[] langs = languages.Trim().Split(',');
            Array.Sort(langs);
            foreach(string abbrev in langs) {
                ListItem itm = null;
                try {
                    itm = new ListItem(CultureInfo.GetCultureInfo(abbrev).EnglishName, abbrev);
                } catch {
                    itm = new ListItem(abbrev);
                }
                itm.Selected = true;
                lstLanguages.Items.Add(itm);
            }
            if(lstLanguages.Items.Count == 0) {
                ListItem itm = new ListItem("No languages found", "");
                itm.Selected = true;
                lstLanguages.Items.Add(itm);
                lstLanguages.Enabled = false;
            } else {
                lstLanguages.Enabled = true;
            }

            lstBundles.Items.Clear();
            cmd.CommandText = "SELECT bundles FROM applications WHERE id = @p";
            reader = cmd.ExecuteReader();
            List<ListItem> tempBundles = new List<ListItem>();
            while(reader.Read()) {
                object rawBundles = reader[0];
                if(rawBundles != null && rawBundles.ToString().Trim().Length > 0) {
                    foreach(string bundle in rawBundles.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) {
                        ListItem itm = new ListItem(bundle,bundle);
                        itm.Selected = true;
                        tempBundles.Add(itm);
                    }
                }
            }
            tempBundles.Sort(new ListComparer());
            foreach(ListItem itm in tempBundles) {
                lstBundles.Items.Add(itm);
            }
            
            if(lstBundles.Items.Count == 0) {
                ListItem itm = new ListItem("All", "");
                itm.Selected = true;
                lstBundles.Items.Add(itm);
                lstBundles.Enabled = false;
            } else {
                lstBundles.Enabled = true;
            }
            reader.Close();
        }
    }
    
    /// <summary>
    /// Adds textboxes for each selected language to the 'New Element' form
    /// </summary>
    private void PopulateAddFormLanguages() {
        pnlDynamicAddTranslation.Controls.Clear();
        foreach(ListItem itm in lstLanguages.Items) {
            if(itm.Selected) {
                Panel div = new Panel();
                div.CssClass = "formrow block";
                TextBox txt = new TextBox();
                txt.ID = "txtDyn" + itm.Value;
                Label lbl = new Label();
                lbl.ID = "lblDyn" + itm.Value;
                lbl.Text = itm.Text;
                lbl.AssociatedControlID = "lblDyn" + itm.Value;
                div.Controls.Add(lbl);
                div.Controls.Add(txt);
                pnlDynamicAddTranslation.Controls.Add(div);
            }
        }
    }

    /// <summary>
    /// Processes the selected bundles and languages and shows a grid to edit the translations.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnEditTranslations_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 2;
        using(Database.Command cmd = db.CreateCommand()) {
            InitializeDataSource();
            cboAddBundleName.DataSource = bundles;
            cboAddBundleName.DataTextField = "Value";
            cboAddBundleName.DataValueField = "Key";
            cboAddBundleName.DataBind();
            PopulateAddFormLanguages();

            InitializeGridView();
            grdTranslations.DataBind();
        }
    }

    /// <summary>
    /// Handles the PageIndexChanging event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdTranslations.PageIndex = e.NewPageIndex;
        grdTranslations.DataBind();
    }

    /// <summary>
    /// Handles the Sorting event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grd_Sorting(object sender, GridViewSortEventArgs e) {
        string oldExpression = grdTranslations.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }
        grdTranslations.DataBind();
    }

    /*/// <summary>
    /// Processes Button clicks in the dynamic part of the grid view. (These are not handles by ASP.NET)
    /// </summary>
    /// <param name="ctrlStr">The UniqueID of the control which was clicked.</param>
    private void ProcessGridViewButtonClick(string ctrlStr) {
        InitializeDataSource();
        InitializeGridView();
        grdTranslations.DataBind();
        Control c = grdTranslations.FindControl(ctrlStr); // give this a go, see if it works, how does it work when editing?
        //GridViewRow r = c.NamingContainer as GridViewRow;
    }*/

    /// <summary>
    /// Sets up the column generator for the translation grid using the selected languages.
    /// </summary>
    private void InitializeGridView() {
        List<string> tmpLanguages = new List<string>();
        foreach(ListItem itm in lstLanguages.Items) {
            if(itm.Selected) {
                tmpLanguages.Add(itm.Value);
            }
        }
        string[] languages = tmpLanguages.ToArray();
        grdTranslations.ColumnsGenerator = new LanguageColumnGenerator(languages);
    }

    /// <summary>
    /// Initializes the TranslationID and main DataSources taking the selected languages and bundles into account.
    /// </summary>
    private void InitializeDataSource() {
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            string applicationId = radApplication.SelectedValue,
                   displayName = string.Empty;
            db.OpenConnection();
            cmd.CommandText = "SELECT displayname, defaultlanguage FROM applications WHERE id = @pID";
            cmd.AddParameterWithValue("@pID", applicationId);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            while(reader.Read()) {
                displayName = reader["displayname"].ToString();
                defaultLanguage = reader["defaultlanguage"].ToString();
            }
            reader.Close();
            lblEditingTranslationsFor.Text = string.Format("Editing Translations for {0}", displayName);

            if(string.IsNullOrEmpty(dsMain.SelectCommand) || translationIds.Count < 5 || bundles.Count == 0) {
                StringBuilder sqlBuffer = new StringBuilder();
                foreach(ListItem itm in lstBundles.Items) {
                    bundles[itm.Value] = itm.Value; // todo: BundleID from bundles table?
                    if(itm.Selected) {
                        sqlBuffer.Append("'" + itm.Value + "', ");
                    }
                }
                sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
                string bundleSql = sqlBuffer.ToString();
                sqlBuffer.Remove(0, sqlBuffer.Length);
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        foreach(ListItem itm in lstLanguages.Items) {
                            if(itm.Selected) {
                                sqlBuffer.Append("MAX(IF(language='").Append(itm.Value).Append("',translation,null)) AS '").Append(itm.Value).Append("'");
                                sqlBuffer.Append(", ");
                            }
                        }
                        break;
                    case Database.DatabaseType.SQLServer:
                        foreach(ListItem itm in lstLanguages.Items) {
                            if(itm.Selected) {
                                sqlBuffer.Append("MAX(CASE WHEN language = '").Append(itm.Value).Append("' THEN translation ELSE NULL END) AS '").Append(itm.Value).Append("'");
                                sqlBuffer.Append(", ");
                            }
                        }
                        break;
                }
                sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
                crossTabColumns = sqlBuffer.ToString();
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        if(lstBundles.Enabled == false) { // bundleless application
                            dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, " + crossTabColumns +
                                                   @"FROM elements INNER JOIN translations ON translations.translationid = elements.translationid 
                                             LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid 
                                             WHERE applicationid = ?pID 
                                             GROUP BY elements.translationid 
                                             ORDER BY bundlename, elementname ";
                        } else {
                            dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, " + crossTabColumns +
                                                   @" FROM elements INNER JOIN translations ON translations.translationid = elements.translationid 
                                              LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid 
                                              WHERE applicationid = ?pID AND bundlename IN (" + bundleSql + @") 
                                              GROUP BY elements.translationid 
                                              ORDER BY bundlename, elementname ";
                        }
                        cmd.CommandText = @"SELECT DISTINCT translationid, 
                                            CAST(IF(CHAR_LENGTH(translation) < 15, 
                                                CONCAT(translationid, ' - ',translation), 
                                                CONCAT(translationid, ' - ',SUBSTRING(translation,1,12),'...')
                                                ) AS CHAR) 
                                            AS display FROM translations WHERE language = @defaultLang ORDER BY translationid ASC";
                        break;
                    case Database.DatabaseType.SQLServer:
                        if(lstBundles.Enabled == false) { // bundleless application
                            dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, " + crossTabColumns +
                                                   @"FROM elements
                                                INNER JOIN translations ON translations.translationid = elements.translationid
                                                LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
                                            WHERE applicationid = @pID 
                                            GROUP BY elements.id,
                                                     applicationid,
                                                     bundlename,
                                                     elementname,
                                                     elements.translationid,
                                                     elements.comment,
                                                     translationcomments.comment
                                            ORDER BY bundlename, elementname";

                        } else {
                            dsMain.SelectCommand = @"SELECT elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment, " + crossTabColumns +
                                                   @"FROM elements
                                                INNER JOIN translations ON translations.translationid = elements.translationid
                                                LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
                                            WHERE applicationid = @pID  AND bundlename IN (" + bundleSql + @") 
                                            GROUP BY elements.id,
                                                     applicationid,
                                                     bundlename,
                                                     elementname,
                                                     elements.translationid,
                                                     elements.comment,
                                                     translationcomments.comment
                                            ORDER BY bundlename, elementname";
                        }
                        cmd.CommandText = @"SELECT DISTINCT translationid, display = CAST(translationid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < 15 THEN translation
                                             ELSE SUBSTRING(translation,1,12)+'...'
                                            END
                                            FROM translations WHERE language = @defaultLang ORDER BY translationid ASC";
                        break;
                }

                grdTranslations.DataSource = dsMain;

                if(dsMain.SelectParameters.Count == 0) {
                    dsMain.SelectParameters.Add("pID", TypeCode.String, applicationId);
                }
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                reader = cmd.ExecuteReader();
                translationIds.Clear();
                translationIds.Add(-1, "New Translation");
                while(reader.Read()) {
                    translationIds.Add(int.Parse(reader["translationid"].ToString()), reader["display"].ToString());
                }
                reader.Close();
            }
            cboAddTranslationID.DataSource = translationIds;
            cboAddTranslationID.DataTextField = "Value";
            cboAddTranslationID.DataValueField = "Key";
            cboAddTranslationID.DataBind();
        }
    }

    /// <summary>
    /// Databinds the BundleName and TranslationID comboboxes and assigns JavaScript listeners.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
    protected void row_DataBound(object sender, GridViewRowEventArgs e) {
        if(e.Row.RowType == DataControlRowType.DataRow) {
            DropDownList cboBundleName = e.Row.FindControl("cboBundleName") as DropDownList;
            cboBundleName.DataSource = bundles;
            cboBundleName.DataTextField = "Value";
            cboBundleName.DataValueField = "Key";
            cboBundleName.DataBind();
            cboBundleName.SelectedValue = (e.Row.FindControl("lblBundleName") as Label).Text;

            DropDownList cboTranslationId = e.Row.FindControl("cboTranslationID") as DropDownList;
            cboTranslationId.DataSource = translationIds;
            cboTranslationId.DataTextField = "Value";
            cboTranslationId.DataValueField = "Key";
            cboTranslationId.DataBind();
            string s = (e.Row.FindControl("lblTranslationId") as Label).Text;
            cboTranslationId.SelectedValue = (e.Row.FindControl("lblTranslationId") as Label).Text;

            // find the JS callback which is assigned to the 'Change Translation' button
            Button btnChangeTranslation = e.Row.FindControl("btnChangeTranslation") as Button;
            string jsChangeTranslation = ClientScript.GetPostBackClientHyperlink(btnChangeTranslation, "");
            cboTranslationId.Attributes["onchange"] = jsChangeTranslation; // assign the callback to the dropdown list so a button press is simulated when the index changes

            TextBox txtElementComment = (e.Row.FindControl("txtElementComment") as TextBox);
            if(!String.IsNullOrEmpty(txtElementComment.Text)) {
                e.Row.Cells[2].ToolTip = txtElementComment.Text;
                e.Row.Cells[2].CssClass = "ssCell commentCell";
            }
            TextBox txtTranslationComment = (e.Row.FindControl("txtTranslationComment") as TextBox);
            if(!String.IsNullOrEmpty(txtTranslationComment.Text)) {
                e.Row.Cells[3].ToolTip = txtTranslationComment.Text;
                e.Row.Cells[3].CssClass = "ssCell commentCell";
            }
        }
    }

    protected void grd_PreRender(object sender, EventArgs e) {
        if(grdTranslations.SelectedIndex >= 0) {
            GridViewRow row = grdTranslations.Rows[grdTranslations.SelectedIndex];
            StringBuilder jqueryBuffer = new StringBuilder(row.UniqueID.Replace('$', '_'));
            jqueryBuffer.Append("_dynfind-").Append(row.RowIndex).Append('-');
            string jsPRefix = jqueryBuffer.ToString();
            jqueryBuffer.Remove(0,jqueryBuffer.Length);
            jqueryBuffer.Append("var prm = Sys.WebForms.PageRequestManager.getInstance(); prm.add_endRequest(function() { ");
            // The control indexes of the textbox and image buttons to which to attach event listeners
            View view;
            TextBox txt;
            ImageButton btn;
            string rowId;
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                rowId = row.Cells[i].ClientID;
                view = (row.Cells[i].Controls[0] as MultiView).Views[1];
                txt = view.Controls[0] as TextBox;
                btn = view.Controls[2] as ImageButton;
                jqueryBuffer.Append("$(\"#").Append(btn.ClientID).Append("\").click(function () {")
                            .Append("$(\"#").Append(rowId).Append("\").find(\"#ajax-").Append(rowId).Append("\").remove();")
                            .Append("$(\"<img/>\").attr({src:\"images/ajax-small.gif\",alt:\"Searching...\",id:\"ajax-").Append(rowId).Append("\",class:\"ajax-small\"}).appendTo(\"#").Append(rowId).Append("\");")
                            .Append("$.ajax({type:\"POST\",url:\"Translations.aspx/FindTranslation\",contentType:\"application/json; charset=utf-8\",")
                                    .Append("data:getTranslationData('").Append(txt.ClientID).Append("','").Append(btn.CommandArgument).Append("'),success: function(response) { ")
                                        .Append("showMatchingTranslations(response, '").Append(rowId).Append("');")
                                    .Append("}")
                            .Append("});return false;")
                            .Append("});");
            }
            jqueryBuffer.Append("});");
            //System.Diagnostics.Debug.WriteLine(jqueryBuffer.ToString());
            
            ScriptManager.RegisterStartupScript(this, typeof(Page), "translationfinder", jqueryBuffer.ToString(), true);
        }
    }

   

    /// <summary>
    /// Handles RowCommand events from the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grd_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        InitializeGridView();
        grdTranslations.DataBind();
        GridViewRow row;  // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row
        switch(e.CommandName) {
            case "edittranslation": // 'Edit' button clicked
                row = grdTranslations.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ShowEditControls(row);
                grdTranslations.SelectedIndex = row.RowIndex;
                break;
            case "changetranslation": // index of dropdownlist changed
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                string selectedTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList).SelectedValue;
                ChangeDisplayedTranslation(row, selectedTranslationID);
                break;
            case "savetranslation": // 'Save' button clicked
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                UpdateTranslation(row, e.CommandArgument.ToString());
                grdTranslations.SelectedIndex = -1;
                break;
            case "deleteelement":
                DeleteElement(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Adds a new element from the form.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnAddElement_Click(object sender, EventArgs e) {
        lblAddError.Visible = false;
        string bundleName = cboAddBundleName.SelectedValue,
               elementName = txtAddElementName.Text,
               elementComment = txtAddElementComment.Text,
               translationID = cboAddTranslationID.SelectedValue,
               translationComment = txtAddTranslationComment.Text;
        if(string.IsNullOrEmpty(elementName)) {
            lblAddError.Text = "Element name cannot be empty";
            lblAddError.Visible = true;
        }
        using(Database.Command cmd = db.CreateCommand()) {
            db.OpenConnection();
            if(translationID == "-1") {
                cmd.CommandText = "SELECT MAX(translationid) FROM translations";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                translationID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                    break;
            }
            
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            foreach(string key in Request.Form.AllKeys) {
                if(key.Contains("txtDyn") && Request.Form[key].Length > 0) {
                    cmd.Parameters["@trans"].Value = Request.Form[key];
                    cmd.Parameters["@lang"].Value = key.Substring(key.LastIndexOf("txtDyn") + 6); // the ID of the textbox has $country-CODE appended to it
                    cmd.ExecuteNonQuery();
                }
            }
            if(translationComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE translationcomments SET comment = @comment WHERE translationid = @tID; IF @@ROWCOUNT = 0 INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", translationID);
                cmd.AddParameterWithValue("@comment", translationComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "INSERT INTO elements (applicationid, bundlename, elementname, translationid, comment) VALUES (@pID, @bundle, @element, @tID, @comment)";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@bundle", bundleName);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            try {
                cmd.ExecuteNonQuery();
                InitializeDataSource();
                InitializeGridView();
                grdTranslations.DataBind();

                cboAddBundleName.SelectedIndex = 0;
                txtAddElementName.Text = string.Empty;
                txtAddElementComment.Text = string.Empty;
                txtAddTranslationComment.Text = string.Empty;
                PopulateAddFormLanguages();
            } catch(Exception ex) {
                lblAddError.Text = "Element name already exists";
                lblAddError.Visible = true;
            }
        }
    }

    /// <summary>
    /// Deletes the specified element.
    /// </summary>
    /// <param name="elementID">The element ID.</param>
    private void DeleteElement(string elementID) {
        using(Database.Command cmd = db.CreateCommand()) {
            db.OpenConnection();
            cmd.CommandText = "DELETE FROM elements WHERE applicationid = @pID AND id = @eID";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@eID", elementID);
            cmd.ExecuteNonQuery();
        }
        grdTranslations.DataBind();
    }

    /// <summary>
    /// Shows the textbox controls to edit a translation.
    /// </summary>
    /// <param name="row">The row.</param>
    private void ShowEditControls(GridViewRow row) {
        if(grdTranslations.SelectedIndex >= 0) {
            GridViewRow oldRow = grdTranslations.SelectedRow;
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                (oldRow.Cells[i].Controls[0] as MultiView).ActiveViewIndex = 0;
            }
        }

        (row.FindControl("mltBundle") as MultiView).ActiveViewIndex = 1;
        (row.FindControl("mltElement") as MultiView).ActiveViewIndex = 1;
        (row.FindControl("mltActions") as MultiView).ActiveViewIndex = 1;
        row.Cells[2].CssClass = "ssCell"; // remove the comment indicators
        row.Cells[3].CssClass = "ssCell";
        for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
            (row.Cells[i].Controls[0] as MultiView).ActiveViewIndex = 1;
        }
    }

    /// <summary>
    /// Changes the translation selected for an element.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="selectedTranslation">The new translation ID.</param>
    private void ChangeDisplayedTranslation(GridViewRow oldRow, string selectedTranslation) {
        GridViewRow row = grdTranslations.Rows[oldRow.RowIndex]; // the index of the old row in the new grid
        ShowEditControls(row);
        (row.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue;
        (row.Cells[2].FindControl("txtElementName") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text;
        (row.Cells[2].FindControl("txtElementComment") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text;
        DropDownList cboTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList);
        cboTranslationID.SelectedValue = selectedTranslation;
        row.CssClass = "ssRowSelected";
        int textBoxControlIndex = 0;
        if(selectedTranslation == "-1") {
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                ((row.Cells[i].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = "";
            }
        } else {
            using(Database.Command cmd = db.CreateCommand()) {
                db.OpenConnection();
                cmd.CommandText = "SELECT " + crossTabColumns + " FROM translations WHERE translationid = @tID";
                cmd.AddParameterWithValue("@tID", selectedTranslation);
                DbDataReader reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    object translation;
                    for(int i = 0; i < reader.FieldCount; i++) {
                        translation = reader[i];
                        ((row.Cells[i + editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = translation == null ? "" : translation.ToString();
                    }
                }
                reader.Close();

                cmd.CommandText = "SELECT comment FROM translationcomments WHERE translationid = @tID";
                reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    (row.Cells[editButtonColumNo].FindControl("txtTranslationComment") as TextBox).Text = reader.GetString(0);
                }
            }
        }
    }

    /// <summary>
    /// Changes the translation displayed on the 'New Element' form.
    /// </summary>
    /// <param name="cboTranslationId">The cbo translation id.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ChangeAddDisplayedTranslation(object cboTranslationId, EventArgs e) {
        int selectedTranslation = int.Parse(cboAddTranslationID.SelectedValue);
        InitializeDataSource();
        InitializeGridView();
        grdTranslations.DataBind();
        using(Database.Command cmd = db.CreateCommand()) {
            db.OpenConnection();
            cmd.CommandText = "SELECT " + crossTabColumns + " FROM translations WHERE translationid = @tID";
            cmd.AddParameterWithValue("@tID", selectedTranslation.ToString());
            DbDataReader reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                object translation;
                for(int i = 0; i < reader.FieldCount; i++) {
                    translation = reader[i];
                    (pnlDynamicAddTranslation.Controls[i].Controls[1] as TextBox).Text = translation == null ? "" : translation.ToString();
                }
            }
            reader.Close();

            cmd.CommandText = "SELECT comment FROM translationcomments WHERE translationid = @tID";
            reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                (pnlDynamicAddTranslation.FindControl("txtAddTranslationComment") as TextBox).Text = reader.GetString(0);
            }
        }
        cboAddTranslationID.SelectedValue = selectedTranslation.ToString();
    }

    /// <summary>
    /// Updates a translation.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="id">The id.</param>
    private void UpdateTranslation(GridViewRow oldRow, string id) {
        GridViewRow newRow = grdTranslations.Rows[oldRow.RowIndex];
        using(Database.Command cmd = db.CreateCommand()) {
            db.OpenConnection();
            Label lblError = grdTranslations.FooterRow.Cells[editButtonColumNo].FindControl("lblError") as Label;
            string bundleName = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue,
                   elementName = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text,
                   elementComment = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text,
                   translationID = (oldRow.Cells[3].FindControl("cboTranslationID") as DropDownList).SelectedValue,
                   translationComment = (oldRow.Cells[3].FindControl("txtTranslationComment") as TextBox).Text;

            if(translationID == "-1") {
                cmd.CommandText = "SELECT MAX(translationid) FROM translations";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                translationID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                    break;
            }
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            string textBoxID;
            for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) {
                textBoxID = (newRow.Cells[i].Controls[0] as MultiView).Views[1].Controls[0].UniqueID;
                foreach(string key in Request.Form.AllKeys) {
                    if(key == textBoxID) {
                        cmd.Parameters["@trans"].Value = Request.Form[key];
                        cmd.Parameters["@lang"].Value = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            if(translationComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE translationcomments SET comment = @comment WHERE translationid = @tID; IF @@ROWCOUNT = 0 INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", translationID);
                cmd.AddParameterWithValue("@comment", translationComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE elements SET bundlename = @bundle, elementname = @element, translationid = @tID, comment = @comment WHERE id = @id";
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@bundle", bundleName);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            cmd.AddParameterWithValue("@id", id);
            try {
                cmd.ExecuteNonQuery();
            } catch {
                lblError.Text = "Element name already exists";
                lblError.Visible = true;
            }
        }
        cboAddTranslationID.DataBind();
        grdTranslations.DataBind();
    }

    /// <summary>
    /// Expands/contracts the grid to fill the whole window
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkExpandScreen_Click(object sender, EventArgs e) {
        if(fullsizeholder.Attributes["class"] == "ssFullSize") {
            lnkExpandScreen.Text = "Expand to fill window";
            lnkExpandScreen.CssClass = "lnkExpand";
            fullsizeholder.Attributes["class"] = "ssContracted";
        } else {
            lnkExpandScreen.Text = "Contract to normal size";
            lnkExpandScreen.CssClass = "lnkContract";
            fullsizeholder.Attributes["class"] = "ssFullSize";
        }
    }

    /// <summary>
    /// Handles AJAX requests to find translations.
    /// </summary>
    /// <param name="language">The language of the translation entered.</param>
    /// <param name="translation">The translation to match against.</param>
    /// <returns>A List of languages/translations/comments</returns>
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    [WebMethod]
    public static List<Translation> FindTranslation(string language, string translation) {
        List<Translation> translationIds = new List<Translation>();
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT translations.translationid, translation, comment FROM translations LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid WHERE language = @language AND translation LIKE '%'+@translation+'%'";
            cmd.AddParameterWithValue("@language", HttpUtility.UrlDecode(language));
            cmd.AddParameterWithValue("@translation", HttpUtility.UrlDecode(translation));
            DbDataReader reader = cmd.ExecuteReader();
            int limit = 5,
                found = 0;
            while(reader.Read() && found < limit) {
                translationIds.Add(new Translation(HttpUtility.UrlEncode(reader[0].ToString()), HttpUtility.UrlEncode(reader[1].ToString()), HttpUtility.UrlEncode(reader[2].ToString())));
                found++;
            }
        }
        db.CloseConnection();
        //foreach(KeyValuePair<string, object> s in translationIds) {System.Diagnostics.Debug.Write(s+"-"+s.Value.);
        return translationIds;
    }

    /// <summary>
    /// A class used by the translation finder service which can hold translation data
    /// </summary>
    public class Translation {
        public string ID { get; set; }
        public string Text { get; set; }
        public string Comment { get; set; }

        public Translation(string id, string text, string comment) {
            this.ID = id;
            this.Text = text;
            this.Comment = comment;
        }
    }

    /// <summary>
    /// Finds translation matching an ID.
    /// </summary>
    /// <param name="translationid">The translation ID to match.</param>
    /// <returns>A key/value collection of languages/translations</returns>
    public static SortedList<string, string> FindTranslation(string translationid) {
        SortedList<string, string> translations = new SortedList<string, string>();
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT language, translation FROM translations WHERE translationid = @tID";
            cmd.AddParameterWithValue("@tID", translationid);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) {
                translations.Add(reader[0].ToString(), reader[1].ToString());
            }
        }
        db.CloseConnection();
        return translations;
    }

/*    #region Render Override
    // Register the dynamically created client scripts
    protected override void Render(HtmlTextWriter writer) {
        // The client events for GridView1 were created in GridView1_RowDataBound
        foreach(GridViewRow r in grdTranslations.Rows) {
            if(r.RowType == DataControlRowType.DataRow) {
                for(int columnIndex = editButtonColumNo; columnIndex < r.Cells.Count; columnIndex++) {
                    Page.ClientScript.RegisterForEventValidation(r.UniqueID + "$ctl00", columnIndex.ToString());
                }
            }
        }

        base.Render(writer);
    }
    #endregion  
  */
}
